﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_CIB005 : DA_Matrix
    {

         public DA_CIB005()
        {
            tableName = "企金";
        }

        public override string[] GetDistinctField()
        {
            return new string[] { "YYYYMM", "CUR" };
        }

        public override System.Data.DataSet GetCriteria()
        {
            //DataSet ds = base.GetCriteria();
            DataSet ds = new DataSet();
            ds.Tables.Add(Utility.GetYYYYMMRange(Convert.ToDateTime("2009-09-01")));
            ds.Tables.Add(Utility.GetCIBCurData());
            DataTable dt, dt1, dt2;
            dt = ds.Tables["YYYYMM"];
            dt1 = dt.Copy(); //dt.Clone();            
            dt1.TableName = "YYYYMM1";
            dt2 = dt.Copy(); //dt.Clone();
            dt2.TableName = "YYYYMM2";
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);


            return ds;
        }


        public DataTable GetSummaryData(string p_ym1,string p_ym2
            ,string p_ftpRate,string p_rate,string p_bal)
        {
            //ResetCriteria();
            FillTableSchema(tableName);
            FillFieldList(new string[] { p_ym1 }
                  , new string[] { "YM1" }
                  , "YYYYMM");
            FillFieldList(new string[] { p_ym2 }
                              , new string[] { "YM2" }
                              , "YYYYMM");
            GetCriteriaString();
            string sql = string.Format( getSqlStatement()
                , getParaString(p_ftpRate, "FTP_RATE")
                , getParaString(p_rate, "承作利率")
                , getParaString(p_bal, "均額")
                , CriteriaString
                , p_bal
                );

            AddParameter(sql);
            return getData();
        }

        protected string getParaString(string p_value, string p_fldName)
        {
            string v1 = string.Empty;

            if(p_value.Length > 0)
            {
                v1 = string.Format(" AND {0} ",string.Format("{0} > @{0}"
                    , p_fldName));
                FillFieldList(new string[] { p_value }, new string[] { p_fldName }
                   , p_fldName);
            }
            return v1;         
        }

        protected string getSqlStatement()
        {
            /*
             * 第三個是 FTP RATE 要大於多少碼
             * 第四個是 承作利率大於多少碼
             * 第五個是 均額大於多少億
             */
            return @"
SELECT 
        A.YYYYMM
	    --,A.LNAC_ID 
	    --,dbo.ReplaceCIBValue(A.帳號,0)LNAC_NAME        
        ,dbo.ReplaceCIBValue(A.LNAC_NAME,1)LNAC_NAME
        ,(SELECT RM_GROUP FROM RM R WHERE R.LNAC_ID = A.LNAC_ID) [客群]
	    ,A.FTP_RATE
	    ,A.承作利率
	    ,A.加減碼
        ,CONVERT(NVARCHAR,CAST(A.均額 AS MONEY),1) 均額
        ,CONVERT(NVARCHAR,CAST(A.餘額 AS MONEY),1) 餘額
	    ,A.CUR 
FROM (
    SELECT *
    FROM 企金 A 
    WHERE A.YYYYMM = @YM1
    AND A.帳號 IN (
                     SELECT
                         A.帳號
                     FROM 企金 A
                     WHERE A.YYYYMM = @YM1

                     EXCEPT 

                     SELECT
                         A.帳號
                     FROM 企金 A
                     WHERE A.YYYYMM = @YM2
                    )
)A 
WHERE 1=1 
{0}
{1}
{2}
{3}
ORDER BY A.LNAC_ID			
";
        }
    }
}
